package net.maku.module.dao;

import net.maku.framework.mybatis.dao.BaseDao;
import net.maku.module.entity.AgContent;
import net.maku.module.entity.AgContentLink;
import net.maku.module.entity.AgContentLogEntity;
import net.maku.module.entity.CategoryTemplateDTO;
import net.maku.module.vo.AgContentCombined;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface AgContentDao extends BaseDao<AgContent> {

    int deleteBatch( @Param("ids") List<Integer> ids);

    List<AgContentCombined> selectContent(Integer templateType, String title);

    List<AgContentCombined> selectContentWithOffset(
            @Param("templateType") Integer templateType,
            @Param("title") String title,
            @Param("offset") int offset,
            @Param("pageSize") int pageSize,
            @Param("id") Integer id,
            @Param("websiteId") Integer websiteId,@Param("params") Map params);


    int countContent(@Param("templateType") Integer templateType,
                     @Param("title") String title,
                     @Param("id") Integer id,
                     @Param("websiteId") Integer websiteId,
                     @Param("params") Map params);


    List<AgContentCombined> selectLinkContent(
            @Param("templateType") Integer templateType,
            @Param("title") String title,
            @Param("offset") int offset,
            @Param("pageSize") int pageSize,
            @Param("id") Integer id,
            @Param("websiteId") Integer websiteId,
            @Param("params") Map params);

    List<AgContentCombined> queryList(Map params);

    Integer getFirstTemplateTypeByCategoryId(@Param("categoryId") Long categoryId);
    int countLinkContent(
            @Param("templateType") Integer templateType,
            @Param("title") String title,
            @Param("id") Integer id,@Param("params") Map params);

    @Insert("INSERT INTO ag_content_link (content_id, link_title, sort, create_time, update_time) " +
            "VALUES (#{contentId}, #{linkTitle}, #{sort}, #{createTime}, #{updateTime})")
    int insertLink(AgContentLink link);

    @Select("SELECT * FROM ag_content_link WHERE content_id = #{id}")
    AgContentLink selectLinkByContentId(Integer id);

    @Update("UPDATE ag_content_link " +
            "SET link_title = #{linkTitle}, " +
            "    sort = #{sort}, " +
            "    update_time = #{updateTime} " +
            "WHERE content_id = #{contentId}")
    void updateLink(AgContentLink link);


    @Select("""
SELECT
    ac.id,
    ac.category_id,
    ac.title,
    ac.content,
    ac.image_url,
    ac.publish_time,
    ac.source,
    ac.is_show,
    ac.is_home,
    ac.template_type,
    ac.status,
    ac.approve_state,
    ac.approve_type,
    ac.creator,
    ac.create_time,
    ac.updater,
    ac.update_time,
    ac.deleted,
    ac.custom_date,
    ac.sort,
    ac.is_announcement,
    ac.keyword,
    ac.href,
    ac.cancellation_time,
    acat.name AS name,
    parent_acat.name AS parentName,
    acl.link_title
FROM ag_content ac
JOIN ag_category acat ON ac.category_id = acat.id
LEFT JOIN ag_content_link acl ON ac.id = acl.content_id
LEFT JOIN ag_category parent_acat ON acat.parent_id = parent_acat.id
WHERE ac.category_id = #{id}
  AND acat.website_id = #{websiteId}
  AND (
      (ac.content IS NOT NULL AND LENGTHB(ac.content) > 0)
      OR (ac.image_url IS NOT NULL AND ac.image_url != '')
      OR (ac.href IS NOT NULL AND ac.href != '')
  )
  AND (
      (ac.category_id IN (1900, 1902) AND ac.sort != 1)
      OR (ac.category_id NOT IN (1900, 1902))
  )
ORDER BY ac.publish_time DESC
OFFSET #{offset} ROWS FETCH NEXT #{pageSize} ROWS ONLY
""")
    List<AgContentCombined> selectContentById(@Param("id") Integer id,
                                                   @Param("websiteId") Integer websiteId,
                                                   @Param("offset") int offset,
                                                   @Param("pageSize") int pageSize);


    List<AgContentCombined> selectShowContent(Integer websiteId);

    @Select("<script>"
            + "SELECT COUNT(*) "
            + "FROM ag_content ac "
            + "LEFT JOIN ag_category category ON ac.category_id = category.id "
            + "WHERE ac.template_type IN (2, 3) "
            + "AND ac.is_show = 0 "
            + "AND ac.image_url IS NOT NULL "
            + "AND category.website_id = #{websiteId} "
            + "</script>")
    int selectNotShowContentCount(@Param("websiteId") Integer websiteId);
    @Select("<script>"
            + "SELECT ac.*, category.name "
            + "FROM ag_content ac "
            + "LEFT JOIN ag_category category ON ac.category_id = category.id "
            + "WHERE ac.template_type IN (2, 3) "
            + "AND ac.is_show = 0 "
            + "AND ac.image_url IS NOT NULL "
            + "AND ac.status = 1 "
            + "AND category.website_id = #{websiteId} "
            + "ORDER BY ac.sort ASC "
            + "LIMIT #{offset}, #{pageSize} "
            + "</script>")
    List<AgContentCombined> selectNotShowContent(@Param("offset") Integer offset,
                                                 @Param("pageSize") Integer pageSize,
                                                 @Param("websiteId") Integer websiteId);


    @Update("UPDATE ag_content "
            + "SET is_show = #{show} "
            + "WHERE id = #{id}")
    int updateShow(@Param("id") Integer id, @Param("show") Integer show);

    @Select("""
SELECT 
    ac.id, 
    ac.title, 
    ac.content, 
    ac.publish_time, 
    acl.link_title, 
    ac.category_id, 
    ac.is_show, 
    c.id AS cId, 
    c.parent_id AS pId, 
    c.name AS cName, 
    parent_c.name AS pName
FROM ag_content ac
LEFT JOIN ag_content_link acl ON ac.id = acl.content_id
LEFT JOIN ag_category c ON ac.category_id = c.id
LEFT JOIN ag_category parent_c ON c.parent_id = parent_c.id
WHERE (
        ac.content LIKE CONCAT('%', #{searchTerm}, '%')
        OR acl.link_title LIKE CONCAT('%', #{searchTerm}, '%')
      )
  AND ac.status = 1
  AND ac.title IS NOT NULL
  AND c.website_id = #{websiteId}
ORDER BY ac.publish_time DESC
OFFSET #{offset} ROWS FETCH NEXT #{pageSize} ROWS ONLY
""")
    List<AgContentCombined> searchFull(@Param("searchTerm") String searchTerm,
                                       @Param("offset") int offset,
                                       @Param("pageSize") int pageSize,
                                       @Param("websiteId") int websiteId);


    @Select("SELECT COUNT(*) " +
            "FROM ag_content ac " +
            "LEFT JOIN ag_content_link acl ON ac.id = acl.content_id " +
            "LEFT JOIN ag_category c ON ac.category_id = c.id " +
            "WHERE (ac.content LIKE CONCAT('%', #{searchTerm}, '%') " +
            "       OR acl.link_title LIKE CONCAT('%', #{searchTerm}, '%')) " +
            "  AND (ac.deleted = 0 OR ac.deleted IS NULL) " +
            "  AND ac.title IS NOT NULL " +
            "  AND ac.status = 1 ")
    int countFullSearchResults(@Param("searchTerm") String searchTerm);
    @Select("""
SELECT 
    ac.id, 
    ac.title, 
    ac.content, 
    ac.publish_time, 
    acl.link_title, 
    ac.category_id, 
    ac.is_show, 
    c.id AS cId, 
    c.parent_id AS pId, 
    c.name AS cName, 
    parent_c.name AS pName
FROM ag_content ac
LEFT JOIN ag_content_link acl ON ac.id = acl.content_id
LEFT JOIN ag_category c ON ac.category_id = c.id
LEFT JOIN ag_category parent_c ON c.parent_id = parent_c.id
WHERE ac.keyword LIKE CONCAT('%', #{searchTerm}, '%')
  AND ac.status = 1
  AND ac.title IS NOT NULL
  AND c.website_id = #{websiteId}
ORDER BY ac.publish_time DESC
OFFSET #{offset} ROWS FETCH NEXT #{pageSize} ROWS ONLY
""")
    List<AgContentCombined> searchByKeyword(@Param("searchTerm") String searchTerm,
                                            @Param("offset") int offset,
                                            @Param("pageSize") int pageSize,
                                            @Param("websiteId") int websiteId);

    @Select("SELECT COUNT(*) " +
            "FROM ag_content ac " +
            "LEFT JOIN ag_category c ON ac.category_id = c.id " +
            "WHERE ac.keyword LIKE CONCAT('%', #{searchTerm}, '%') " +
            "  AND ac.status = 1 " +
            "  AND ac.title IS NOT NULL " +
            "  AND c.website_id = #{websiteId}")
    int countByKeyword(@Param("searchTerm") String searchTerm,
                       @Param("websiteId") int websiteId);

    @Insert("INSERT INTO ag_content_log (user_id, update_time, module_name, type, success) " +
            "VALUES (#{userId}, #{updateTime}, #{moduleName}, #{type}, #{success})")
    void insertLog(AgContentLogEntity agContentLogEntity);


    @Select("SELECT id, update_time, user_id, module_name, type, success " +
            "FROM ag_content_log " +
            "WHERE (#{type} IS NULL OR type = #{type}) " +
            "AND (#{success} IS NULL OR success = #{success}) " +
            "ORDER BY update_time DESC " +
            "LIMIT #{offset}, #{pageSize}")
    List<AgContentLogEntity> selectContentLogs(@Param("type") Integer type,
                                               @Param("success") Integer success,
                                               @Param("offset") int offset,
                                               @Param("pageSize") int pageSize);

    @Select("SELECT COUNT(*) " +
            "FROM ag_content_log " +
            "WHERE (#{type} IS NULL OR type = #{type}) " +
            "AND (#{success} IS NULL OR success = #{success})")
    int selectContentLogsCount(@Param("type") Integer type, @Param("success") Integer success);

    Integer selectSortMax();
    @Select("""
SELECT
    ac.id,
    ac.category_id,
    ac.title,
    ac.content,
    ac.image_url,
    ac.publish_time,
    ac.source,
    ac.is_show,
    ac.is_home,
    ac.template_type,
    ac.status,
    ac.approve_state,
    ac.approve_type,
    ac.creator,
    ac.create_time,
    ac.updater,
    ac.update_time,
    ac.deleted,
    ac.custom_date,
    ac.sort,
    ac.is_announcement,
    ac.keyword,
    ac.href,
    ac.cancellation_time,
    acat.name AS name,
    parent_acat.name AS parentName,
    acl.link_title
FROM ag_content ac
JOIN ag_category acat ON ac.category_id = acat.id
LEFT JOIN ag_content_link acl ON ac.id = acl.content_id
LEFT JOIN ag_category parent_acat ON acat.parent_id = parent_acat.id
WHERE ac.category_id = #{id}
  AND acat.website_id = #{websiteId}
  AND (
      (ac.content IS NOT NULL AND LENGTHB(ac.content) > 0)
      OR (ac.image_url IS NOT NULL AND ac.image_url != '')
      OR (ac.href IS NOT NULL AND ac.href != '')
  )
  AND (
      (ac.category_id IN (1900, 1902) AND ac.sort != 1)
      OR (ac.category_id NOT IN (1900, 1902))
  )
ORDER BY ac.publish_time DESC
OFFSET #{offset} ROWS FETCH NEXT #{pageSize} ROWS ONLY
""")
    List<AgContentCombined> selectAllContentById(@Param("id") Integer id,
                                                 @Param("websiteId") Integer websiteId,
                                                 @Param("offset") int offset,
                                                 @Param("pageSize") int pageSize);
    @Select("select ac.*, al.link_title, acy.name as sonName, acy.id as sonId, acy2.id as parentId, acy2.name as parentName from ag_content ac left join ag_content_link al on al.content_id = ac.id left join ag_category acy on ac.category_id = acy.id left join ag_category acy2 on acy.parent_id = acy2.id where ac.is_home = '1'")
    List<AgContentCombined> getHomeContent();
    @Select("select ac.*, al.link_title, acy.name as sonName, acy.id as sonId, acy2.id as parentId, acy2.name as parentName from ag_content ac left join ag_content_link al on al.content_id = ac.id left join ag_category acy on ac.category_id = acy.id left join ag_category acy2 on acy.parent_id = acy2.id where ac.is_announcement = '1'")
    List<AgContentCombined> getAnnouncement();

    void setNotHome(Integer id);

    List<AgContent> selectFirstByCategoryIds(List<Integer> categoryIds);

    @Select("""
SELECT COUNT(1)
FROM ag_content ac
JOIN ag_category acat ON ac.category_id = acat.id
WHERE ac.category_id = #{id}
  AND acat.website_id = #{websiteId}
  AND (
      (ac.content IS NOT NULL AND LENGTHB(ac.content) > 0)
      OR (ac.image_url IS NOT NULL AND ac.image_url != '')
      OR (ac.href IS NOT NULL AND ac.href != '')
  )
  AND (
      (ac.category_id IN (1900, 1902) AND ac.sort != 1)
      OR (ac.category_id NOT IN (1900, 1902))
  )
""")
    int countContentById(@Param("id") Integer id, @Param("websiteId") Integer websiteId);

    @Select("select ac.*,acl.link_title from ag_content ac left join ag_content_link acl on acl.content_id = ac.id where ac.id = #{id}")
    AgContentCombined queryById(Integer id);
    @Select("select * from ag_content where category_id = #{id} and sort = 1")
    AgContentCombined getSortIsOne(Integer id);

    @Select({
            "<script>",
            "SELECT category_id, ",
            "       (SELECT template_type ",
            "          FROM ag_content ac2 ",
            "         WHERE ac2.category_id = ac.category_id ",
            "         ORDER BY ac2.id ASC ",
            "         LIMIT 1) AS template_type ",
            "FROM ag_content ac ",
            "WHERE category_id IN ",
            "<foreach item='id' collection='categoryIds' open='(' separator=',' close=')'>",
            "  #{id}",
            "</foreach>",
            "GROUP BY category_id",
            "</script>"
    })
    List<Map<String, Object>> selectCategoryTemplateTypes(@Param("categoryIds") List<Long> categoryIds);

    @Select("""
    SELECT ac.category_id, ac.template_type
    FROM ag_content ac
    INNER JOIN (
        SELECT category_id, MIN(id) AS first_id
        FROM ag_content
        WHERE status = 1
        GROUP BY category_id
    ) first_content ON ac.id = first_content.first_id
""")
    List<CategoryTemplateDTO> getFirstContentTemplateType();
    @Update("""
    update ag_content set status = #{status} where id =#{id}
""")
    void updateStatusByContentId(AgContentCombined agContentCombined);
}
